package cetcbigdata.da.base;

import com.alibaba.fastjson.JSONObject;
import java.sql.*;
import java.util.List;

/**
 * @author YuanGongPing
 * @version 0.1
 * @description MysqlDb
 * @since 2022/9/28 17:32
 */


public class MysqlDb {
    Connection connection = null;
    String ip = "127.0.0.1";
//    String ip = "172.16.119.5";
    String port = "3306";
    String dbName = "chengdu";
    String userName = "root";
    String password = "root";

    public MysqlDb(){
        try {
            this.connection = DriverManager.getConnection(
                    String.format("jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", this.ip, this.port, this.dbName),
                    this.userName,
                    this.password);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public Connection getConnection(){

          return this.connection;
    }

    public MysqlDb(String ip, String port, String dbName, String userName, String password){
        try {
            this.ip = ip;
            this.port = port;
            this.dbName = dbName;
            this.userName = userName;
            this.password = password;
            this.connection = DriverManager.getConnection(
                    String.format("jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8", ip, port, dbName),
                    userName,
                    password);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void clearTable(Connection connection, String tableName) {
        PreparedStatement preparedStatement = null;
        try {
            connection.setAutoCommit(true);
            //这里需要注意，SQL语句的格式必须是预处理的这种，就是values(?,?,...,?)，否则批处理不起作用
            Statement statement = connection.createStatement();
            statement.execute("truncate table " + tableName);
        } catch (SQLException es) {
           es.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeDBResources(preparedStatement, null);
        }
    }

    public static void doBatchInsert(Connection connection, List<JSONObject> dataList, String tableName) throws SQLException {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            //这里需要注意，SQL语句的格式必须是预处理的这种，就是values(?,?,...,?)，否则批处理不起作用
            statement = connection.prepareStatement(
                    String.format("insert into %s(`region`,`module`,`list_url`,`list_page_html`,`detail_url`,`detail_html`,`content`) values(?,?,?,?,?,?,?)", tableName)
            );
            // 塞数据
            for (JSONObject dataItem : dataList) {
                statement.setString(1, dataItem.getString("region"));
                statement.setString(2, dataItem.getString("module"));
                statement.setString(3, dataItem.getString("list_url"));
                statement.setString(4, dataItem.getString("list_page_html"));
                statement.setString(5, dataItem.getString("detail_url"));
                statement.setString(6, dataItem.getString("detail_html"));
                statement.setString(7, dataItem.getString("content"));
                //将要执行的SQL语句先添加进去，不执行
                statement.addBatch();
            }
            statement.executeBatch();
            statement.clearBatch();
            connection.commit();
        } catch (SQLException e) {
            System.out.println("回滚此次写入, 采用每次写入一行方式提交. 因为:" + e.getMessage());
            connection.rollback();
        } catch (Exception e) {
           e.printStackTrace();
        } finally {
            closeDBResources(statement, null);
        }
    }

    public static void closeDBResources(PreparedStatement stmt, Connection conn) {
        closeDBResources(null, stmt, conn);
    }

    public static void closeDBResources(ResultSet rs, Statement stmt,
                                        Connection conn) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException unused) {
            }
        }

        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException unused) {
            }
        }

        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException unused) {
            }
        }
    }
}
